package com.audi.box.adapter;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

import com.audi.dto.DeviceItem;
import com.audi.dto.PlaceItem;
import com.audi.utils.DeviceHelper;

/**
 * @author NamNT17/HuyPVA - Modify by CuongPH7
 * 
 */
public class SQLiteAsusHelperDevice extends SQLiteAsusHelperCommon {
    /**
     * Class constructor specifying Context to create.
     * 
     * @param context
     *            android.content.Context
     * 
     */
    public SQLiteAsusHelperDevice(Context context) {
        super(context);
    }
    
    /**
     * Add a Device Item
     * <p>
     * Modify 06/09 - CuongPH7
     * 
     * @param device
     * @return Device Id if successful, 0 if device exist
     */
    public int addDevice(DeviceItem device) {
        Log.d("SQLite Helper Device", "addDevice");
        
        ContentValues values = convertDeviceItemToContentValue(device);
        int id = sqlInsert(TABLE_DEVICE, null, values);
        
        // add history
        // updateHistoryDevice(id, 1, 0); HuyPVA-
        
        return id;
    }
    
    /**
     * Update Device Item.
     * 
     * @param device
     *            Device Item
     * @return 1 if (successful, 0 if (device does not exist
     */
    public int updateDevice(DeviceItem device) {
        Log.d("SQLite Helper Device", "updateDevice");
        int vRet = 0;
        if (device != null) {
            SQLiteDatabase db = this.getWritableDatabase();
            try {
                ContentValues values = convertDeviceItemToContentValue(device);
                
                vRet = db.update(TABLE_DEVICE, values, KEY_DEVICE_ID + " = ? ",
                        new String[] {String.valueOf(device.getDeviceId())});
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (db != null) {
                    db.close();
                }
            }
        }
        
        return vRet;
    }
    
    /**
     * rename a Device Item
     * <p>
     * Modify 06/09 - CuongPH7
     * 
     * @author NamNT16
     * @deprecated {@link #updateDevice(DeviceItem)}
     * @param device
     * @return 1 if (successful, 0 if (device exist
     */
    @Deprecated
    public int renameDevice(DeviceItem device) {
        Log.d("SQLite Helper Device", "renameDevice");
        ContentValues values = new ContentValues();
        values.put(KEY_DEVICE_NAME, device.getDeviceName());
        
        return sqlUpdate(TABLE_DEVICE, values,
                KEY_DEVICE_ID + "=" + String.valueOf(device.getDeviceId()),
                null);
    }
    
    /**
     * Delete a Device Item
     * <p>
     * Modify 06/09 - CuongPH7
     * 
     * @author NamNT16
     * @param device
     * @return 1 if successful, 0 if device exist
     */
    public int deleteDevice(DeviceItem device) {
        Log.d("SQLite Helper Device", "deleteDevice");
        ContentValues values = new ContentValues();
        values.put(KEY_DEVICE_IS_DELETED, 1);
        
        int id = sqlUpdate(TABLE_DEVICE, values,
                KEY_DEVICE_ID + "=" + String.valueOf(device.getDeviceId()),
                null);
        
        // update history
        // updateHistoryDevice(item.getDeviceId(), 1, 1); HuyPVA-
        
        return id;
    }
    
    /**
     * Change Device's Place
     * <p>
     * Modify 06/09 - CuongPH7
     * 
     * @deprecated {@link #updateDevice(DeviceItem)}
     * @author NamNT16
     * @param device
     * @return 1 if (successful, 0 if (device exist
     */
    @Deprecated
    public int editPlaceDevice(DeviceItem item) {
        Log.d("SQLite Helper Device", "editPlaceDevice");
        ContentValues values = new ContentValues();
        values.put(KEY_DEVICE_PLACE_ID, item.getDevicePlaceId());
        
        return sqlUpdate(TABLE_DEVICE, values,
                KEY_DEVICE_ID + "=" + String.valueOf(item.getDeviceId()), null);
    }
    
    /**
     * Get Device Item List
     * <p>
     * Modify 06/09 - CuongPH7
     * 
     * @deprecated {@link #getListDevice(0)}
     * @param device
     * @return 1 if (successful, 0 if (device exist
     */
    @Deprecated
    public ArrayList<DeviceItem> getListDeviceTrigg() {
        Log.d("SQLite Helper Device", "getListDeviceTrigg");
        ArrayList<DeviceItem> list = new ArrayList<DeviceItem>();
        String selectQuery = "SELECT * FROM " + TABLE_DEVICE;
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    
                    DeviceItem device = createDeviceItem(cursor);
                    list.add(device);
                    
                } while (cursor.moveToNext());
            }
            return list;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    /**
     * @deprecated {@link #getDeviceById(int)}
     * @author Namnt16
     * @param deviceId
     * @return DeviceItem
     * @see #getDeviceById(int)
     */
    // get device from smart trigger
    @Deprecated
    public DeviceItem getDeviceByfromTrigger(int deviceId) {
        Log.d("SQLite Helper Device", "getDeviceByfromTrigger");
        return getDeviceById(deviceId);
    }
    
    /**
     * Get Device Item by Device Id
     * 
     * @param deviceId
     *            {@link DeviceItem}
     * @return DeviceItem
     */
    public DeviceItem getDeviceById(int deviceId) {
        Log.d("SQLite Helper Device", "getDeviceById");
        
        /*
         * DeviceItem deviceItem = new DeviceItem(); String selectQuery =
         * "SELECT * FROM " + TABLE_DEVICE + " WHERE " + KEY_DEVICE_ID + " = '"
         * + deviceId + "' AND " + KEY_DEVICE_IS_DELETED + " != 1";
         * 
         * SQLiteDatabase db = null; Cursor cursor = null; try { db =
         * this.getReadableDatabase(); cursor = db.rawQuery(selectQuery, null);
         * if (cursor.moveToFirst()) { deviceItem = createDeviceItem(cursor); }
         * db.close();
         * 
         * } catch (Exception e) { Log.e("DATABASE getDeviceById",
         * e.getMessage()); } finally { if (cursor != null) { cursor.close(); }
         * if (db != null) { db.close(); }
         * 
         * } return deviceItem;
         */
        
        int[] lstDeviceId = {deviceId};
        
        ArrayList<DeviceItem> lstDevice = getDeviceById(lstDeviceId);
        
        if (lstDevice != null && lstDevice.size() > 0) {
            return lstDevice.get(0);
        } else {
            return new DeviceItem();
        }
        
    }
    
    /**
     * Get Device Item by Device Id
     * 
     * @param deviceId
     *            {@link DeviceItem}
     * @return DeviceItem
     */
    public ArrayList<DeviceItem> getDeviceById(int[] lstDeviceId) {
        Log.d("SQLite Helper Device", " getDeviceById(int[])");
        
        return getListDevice(0, 0, lstDeviceId);
    }
    
    /**
     * Get Device item list by Device Group Type (ONOFF / WARNING / ALRM).
     * Modify 11/09
     * 
     * @author CuongPH7 - Added 25/08
     * @param deviceGroupType
     *            {@link DeviceItem}
     * @return An ArrayList DeviceItem
     * @see #getListDevice(int, int)
     */
    public ArrayList<DeviceItem> getListDevice(int deviceGroupType) {
        return getListDevice(deviceGroupType, 0, null);
    }
    
    /**
     * Get Device item list by Device favorite.
     * <p>
     * Require by NhanNLT
     * 
     * @author CuongPH7 - Added 25/08
     * @return An ArrayList DeviceItem
     * @see #getListDevice(int, int, int[])
     */
    public ArrayList<DeviceItem> getListDeviceFavorite() {
        return getListDevice(0, 1, null);
    }
    
    /**
     * Get Device item list.
     * 
     * @author CuongPH7 - Added 11/09
     * @return An ArrayList DeviceItem
     * @see #getListDevice(int, int, int[])
     */
    public ArrayList<DeviceItem> getListDevice() {
        return getListDevice(0, 0, null);
    }
    
    /**
     * Get Device item list by Device Group Type AND Device favorite.
     * 
     * @author CuongPH7 - Added 11/09
     * @param deviceGroupType
     *            {@link DeviceItem}
     * @return An ArrayList DeviceItem
     */
    public ArrayList<DeviceItem> getListDevice(int deviceGroupType,
            int isFavorite, int[] lstDeviceId) {
        ArrayList<DeviceItem> list = new ArrayList<DeviceItem>();
        
        String selectQuery = "SELECT "
                + TABLE_DEVICE + "." + KEY_DEVICE_ID + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_NAME + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_CODE + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_ICON + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_TYPE + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_CONTROL_VALUE + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_STATUS + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_USER_ID + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_PLACE_ID + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_FAVORITE + ", "
                
                + TABLE_USER + "." + KEY_USER_ICON + ", "
                + TABLE_USER + "." + KEY_USER_STATUS + ", "
                
                + TABLE_PLACE + "." + KEY_PLACE_NAME + ", "
                + TABLE_PLACE + "." + KEY_PLACE_CAPABILITY_TYPE
                
                + " FROM " + TABLE_DEVICE
                
                + " LEFT JOIN " + TABLE_PLACE + " ON " + TABLE_PLACE + "."
                + KEY_PLACE_ID + " = " + TABLE_DEVICE + "."
                + KEY_DEVICE_PLACE_ID
                
                + " LEFT JOIN " + TABLE_USER + " ON " + TABLE_USER + "."
                + KEY_USER_ID + " = " + TABLE_DEVICE + "." + KEY_DEVICE_USER_ID;
        
        ArrayList<String> where = new ArrayList<String>();
        
        // GET BY GROUP TYPE
        if (deviceGroupType != 0) {
            ArrayList<Integer> listType = DeviceHelper
                    .getListType(deviceGroupType);
            
            String whereGroupType = TABLE_DEVICE + "." + KEY_DEVICE_TYPE
                    + " IN (" + TextUtils.join(",", listType) + ")";
            
            where.add(whereGroupType);
        }
        
        // GET BY GROUP TYPE
        if (isFavorite != 0) {
            String whereFavorite = TABLE_DEVICE + "." + KEY_DEVICE_FAVORITE
                    + " >0";
            where.add(whereFavorite);
        }
        
        /*
         * ignore deleted device
         */
        where.add(TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED + " = 0 ");
        
        /*
         * GET Device By Device Id list
         */
        if (lstDeviceId != null && lstDeviceId.length > 0) {
            ArrayList<String> lstWhere = new ArrayList<String>();
            
            for (int deviceId : lstDeviceId) {
                lstWhere.add(KEY_DEVICE_ID + " = '" + deviceId + "'");
            }
            
            where.add(" (" + TextUtils.join(" OR ", lstWhere) + ") ");
        }
        
        selectQuery += " WHERE " + TextUtils.join(" AND ", where);
        
        selectQuery += " ORDER BY " + TABLE_DEVICE + "." + KEY_DEVICE_FAVORITE
                + " DESC";
        
        Log.e("DATABASE TEST getListDevice(int, int, int[])", selectQuery);
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    int columnIndex = 0;
                    DeviceItem device = createDeviceItem(cursor);
                    
                    /* int userId = Integer.parseInt(cursor.getString(6)); */
                    int userId = device.getUserId();
                    if (userId > 0) {
                        device.setUserId(userId);
                        device.setShowUserStatus(1);
                        
                        columnIndex = cursor
                                .getColumnIndexOrThrow(KEY_USER_ICON);
                        if (!cursor.isNull(columnIndex)) {
                            device.setUserIcon(cursor.getString(columnIndex));
                        }
                        
                        columnIndex = cursor
                                .getColumnIndexOrThrow(KEY_USER_STATUS);
                        if (!cursor.isNull(columnIndex)) {
                            device.setUserStatus(cursor.getInt(columnIndex));
                        }
                        
                    } else {
                        device.setUserId(0);
                        device.setShowUserStatus(0);
                        device.setUserIcon("");
                        device.setUserStatus(0);
                    }
                    
                    columnIndex = cursor.getColumnIndexOrThrow(KEY_PLACE_NAME);
                    device.setDevicePlace(cursor.getString(columnIndex));
                    
                    columnIndex = cursor
                            .getColumnIndexOrThrow(KEY_PLACE_CAPABILITY_TYPE);
                    if (!cursor.isNull(columnIndex)) {
                        device.setCapability(cursor.getInt(columnIndex));
                    }
                    
                    list.add(device);
                    
                } while (cursor.moveToNext());
            }
            return list;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    /**
     * Get all new Device Item list
     * 
     * @author CuongPH7 - 25/08
     * @return An ArrayList new Device Item
     */
    public ArrayList<DeviceItem> getNewDevice() {
        ArrayList<DeviceItem> list = new ArrayList<DeviceItem>();
        String selectQuery = "SELECT " + TABLE_DEVICE + "." + KEY_DEVICE_ID
                + ", " + TABLE_DEVICE + "." + KEY_DEVICE_NAME + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_ICON + ", " + TABLE_DEVICE
                + "." + KEY_DEVICE_TYPE + ", " + TABLE_DEVICE + "."
                + KEY_DEVICE_CONTROL_VALUE + ", " + TABLE_DEVICE + "."
                + KEY_DEVICE_STATUS + ", " + TABLE_DEVICE + "."
                + KEY_DEVICE_USER_ID + ", " + TABLE_DEVICE + "."
                + KEY_DEVICE_PLACE_ID + ", "
                
                + TABLE_PLACE + "." + KEY_PLACE_NAME
                
                + TABLE_USER + "." + KEY_USER_ICON + ", " + TABLE_USER + "."
                + KEY_USER_STATUS + ", "
                
                + " FROM " + TABLE_DEVICE
                
                + " LEFT OUTER JOIN " + TABLE_PLACE + " ON " + TABLE_PLACE
                + "." + KEY_PLACE_ID + " = " + TABLE_DEVICE + "."
                + KEY_DEVICE_PLACE_ID
                
                + " LEFT OUTER JOIN " + TABLE_USER + " ON " + TABLE_USER + "."
                
                + KEY_USER_ID + " = " + TABLE_DEVICE + "." + KEY_DEVICE_USER_ID;
        
        ArrayList<String> where = new ArrayList<String>();
        
        /*
         * get device not named
         */
        where.add(TABLE_DEVICE + "." + KEY_DEVICE_NAME + " = '' ");
        
        /*
         * get deleted device
         */
        where.add(TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED + " = 1 ");
        
        selectQuery += " WHERE " + TextUtils.join(" OR ", where);
        
        selectQuery += " ORDER BY " + TABLE_DEVICE + "." + KEY_DEVICE_ID
                + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    
                    DeviceItem device = createDeviceItem(cursor);
                    list.add(device);
                    
                } while (cursor.moveToNext());
            }
            return list;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    /**
     * Get Device Item list by Place Id
     * 
     * @param placeId
     *            Place Id
     * @return An ArrayList Device Item
     */
    public ArrayList<DeviceItem> getListDeviceByPlaceId(int placeId) {
        Log.d("SQLite Helper Device", "getListDeviceByPlaceId");
        ArrayList<DeviceItem> listDevice = new ArrayList<DeviceItem>();
        
        /*
         * 
         * String sql = "SELECT "+ TABLE_DEVICE+ "."+ KEY_DEVICE_ID+ "," +
         * TABLE_DEVICE+ "."+ KEY_DEVICE_NAME+ ","+ TABLE_DEVICE + "."+
         * KEY_DEVICE_ICON+ ","+ TABLE_DEVICE+ "." + KEY_DEVICE_STATUS+ ","+
         * TABLE_DEVICE+ "." + KEY_DEVICE_TYPE+ ","+ TABLE_DEVICE+ "." +
         * KEY_DEVICE_CONTROL_VALUE+ ","+ TABLE_DEVICE+ "." + KEY_DEVICE_CODE+
         * ","+ TABLE_DEVICE+ "." + KEY_DEVICE_BOX_ID+ ","+ TABLE_DEVICE+ "." +
         * KEY_DEVICE_PLACE_ID+ ","+ TABLE_DEVICE+ "." + KEY_DEVICE_USER_ID+
         * ","+ TABLE_PLACE+ "."+ KEY_PLACE_NAME + ","+ TABLE_DEVICE+ "."+
         * KEY_DEVICE_IS_DELETED;
         */
        String selectQuery = "SELECT " + TABLE_DEVICE + "." + KEY_DEVICE_ID
                + "," + TABLE_DEVICE + "." + KEY_DEVICE_NAME + ","
                + TABLE_DEVICE + "." + KEY_DEVICE_ICON + "," + TABLE_DEVICE
                + "." + KEY_DEVICE_STATUS + "," + TABLE_DEVICE + "."
                
                + KEY_DEVICE_TYPE + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_CONTROL_VALUE + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_CODE + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_BOX_ID + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_PLACE_ID + "," + TABLE_DEVICE + "."
                + KEY_DEVICE_USER_ID + "," + TABLE_PLACE + "." + KEY_PLACE_NAME
                + "," + TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED
                
                + " FROM " + TABLE_DEVICE
                + " LEFT JOIN " + TABLE_PLACE + " ON "
                + TABLE_DEVICE + "." + KEY_DEVICE_PLACE_ID + " = "
                + TABLE_PLACE + "." + KEY_PLACE_ID;
        
        // sql+= " WHERE "+ KEY_PLACE_ID+ "="+ placeId;
        
        selectQuery += " WHERE " + KEY_PLACE_ID + "=" + placeId;
        selectQuery += " AND " + TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED
                + " = 0 ";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        try {
            if (cursor.moveToFirst() /* && cursor2.moveToFirst() */) {
                do {
                    
                    DeviceItem device = createDeviceItem(cursor);
                    
                    int columnIndex = cursor
                            .getColumnIndexOrThrow(KEY_PLACE_NAME);
                    
                    if (!cursor.isNull(columnIndex)) {
                        device.setDevicePlace(cursor.getString(columnIndex));
                    }
                    
                    listDevice.add(device);
                    
                } while (cursor.moveToNext());
                
            }
            
        } catch (Exception e) {
            Log.e("DATABASE getListDeviceByPlaceId", e.getMessage());
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
        return listDevice;
    }
    
    /**
     * Get Device Item List by User
     * 
     * @param userId
     *            User Id
     * @return An ArrayList Device Item
     */
    public ArrayList<DeviceItem> getDeviceByUserId(int userId) {
        Log.d("SQLite Helper Device", "getDeviceByUserId");
        ArrayList<DeviceItem> arrDeviceItem = new ArrayList<DeviceItem>();
        String selectQuery = "SELECT " + TABLE_DEVICE + "." + KEY_DEVICE_ID
                + ", " + TABLE_DEVICE + "." + KEY_DEVICE_NAME + ", "
                + TABLE_DEVICE + "." + KEY_DEVICE_USER_ID + ", " + TABLE_PLACE
                + "." + KEY_PLACE_ID + "," + TABLE_PLACE + "."
                + KEY_PLACE_CAPABILITY_TYPE + "," + TABLE_PLACE + "."
                + KEY_PLACE_NAME + "," + "COUNT (" + TABLE_DEVICE + "."
                + KEY_DEVICE_ID + ") as DEVICE_NUM" + "," + TABLE_USER + "."
                + KEY_USER_NAME;
        
        selectQuery += " FROM " + TABLE_DEVICE + " LEFT OUTER JOIN "
                + TABLE_PLACE + " ON " + TABLE_DEVICE + "."
                + KEY_DEVICE_PLACE_ID + " = " + TABLE_PLACE + "."
                + KEY_PLACE_ID + " LEFT OUTER JOIN " + TABLE_USER + " ON "
                + TABLE_USER + "." + KEY_USER_ID + " = " + TABLE_DEVICE + "."
                + KEY_DEVICE_USER_ID;
        
        selectQuery += " AND " + TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED;
        
        selectQuery += " WHERE " + TABLE_PLACE + "." + KEY_PLACE_IS_DELETED
                
                + " = 0 ";
        selectQuery += " GROUP BY " + TABLE_PLACE + "." + KEY_PLACE_ID;
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    
                    int columnIndex = 0;
                    
                    DeviceItem deviceItem = createDeviceItem(cursor);
                    
                    columnIndex = cursor
                            .getColumnIndexOrThrow(KEY_PLACE_CAPABILITY_TYPE);
                    if (!cursor.isNull(columnIndex)) {
                        deviceItem.setCapability(cursor.getInt(columnIndex));
                    }
                    
                    columnIndex = cursor.getColumnIndexOrThrow(KEY_PLACE_NAME);
                    if (!cursor.isNull(columnIndex)) {
                        deviceItem
                                .setDevicePlace(cursor.getString(columnIndex));
                    }
                    
                    columnIndex = cursor.getColumnIndexOrThrow("DEVICE_NUM");
                    if (!cursor.isNull(columnIndex)) {
                        deviceItem.setCountDevice(cursor.getInt(columnIndex));
                    }
                    
                    arrDeviceItem.add(deviceItem);
                    
                } while (cursor.moveToNext());
            }
            return arrDeviceItem;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        
    }
    
    /**
     * Check a Device Item exist
     * 
     * @author CuongPH7 - 25/08
     * @param device
     *            DeviceItem
     * @return <code>True</code> if (DeviceItem Exist <code>False</code> if
     *         (DeviceItem does not Exist
     */
    public boolean checkExistDevice(DeviceItem device) {
        String selectQuery = "SELECT " + KEY_DEVICE_ID + " FROM "
                + TABLE_DEVICE + " WHERE " + KEY_DEVICE_ID + " = "
                + device.getDeviceId() + " AND " + KEY_DEVICE_IS_DELETED
                + " =0";
        
        Log.e("DATABASE ERROR checkExistDevice", selectQuery);
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            return true;
        }
        return false;
    }
    
    /**
     * Check a Device Item exist By Device Code
     * 
     * @author CuongPH7 - 25/08
     * @param device
     *            DeviceItem
     * @return <code>True</code> if (Device Code Exist <code>False</code> if
     *         (Device Code does not Exist
     */
    public boolean checkExistDeviceByCode(DeviceItem device) {
        String selectQuery = "SELECT " + KEY_DEVICE_CODE + " FROM "
                + TABLE_DEVICE + " WHERE " + KEY_DEVICE_CODE + "='"
                + device.getDeviceCode() + "'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            return true;
        }
        return false;
    }
    
    /**
     * Check a Device Item exist By Device Name
     * 
     * @author CuongPH7 - 06/09
     * @param device
     *            DeviceItem
     * @return <code>True</code> if (Device Name Exist <code>False</code> if
     *         (Device Name does not Exist
     */
    public boolean checkExistDeviceByName(DeviceItem item) {
        String selectQuery = "SELECT " + KEY_DEVICE_CODE + " FROM "
                + TABLE_DEVICE + " WHERE " + KEY_DEVICE_NAME + "='"
                + item.getDeviceName() + "'" + " AND " + TABLE_DEVICE + "."
                + KEY_DEVICE_IS_DELETED + " =0" + " AND " + TABLE_DEVICE + "."
                + KEY_DEVICE_ID + " <> "
                + ((item.getDeviceId() == null) ? 0 : item.getDeviceId());
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            return true;
        }
        return false;
    }
    
    /**
     * Delete Device Item
     * 
     * @param device
     * @return 1 if (successful, 0 if (device exist
     */
    public int deleteDeviceById(DeviceItem device) {
        Log.d("SQLite Helper Device", "deleteDeviceById");
        ContentValues values = new ContentValues();
        values.put(KEY_DEVICE_IS_DELETED, 1);
        
        return sqlUpdate(TABLE_DEVICE, values,
                KEY_DEVICE_ID + "=" + String.valueOf(device.getDeviceId()),
                null);
    }
    
    /**
     * @deprecated {@link #deleteDeviceById(DeviceItem)}
     */
    @Deprecated
    public void deleteDeviceItem(DeviceItem deviceItem) {
        /*
         * Log.d("SQLite Helper Device", "deleteDeviceItem");
         * getWritableDatabase().delete(TABLE_DEVICE, KEY_DEVICE_ID + " = ?",
         * new String[] { String.valueOf(deviceItem.getDeviceId()) }); // update
         * history
         */
        
    }
    
    // get id new device
    /**
     * Unknow Function
     * 
     * @deprecated
     * @return
     */
    @Deprecated
    public int getListDeviceToGetId() {
        return -1;
        /*
         * Log.d("SQLite Helper Device", "getListDeviceToGetId");
         * ArrayList<DeviceItem> list = new ArrayList<DeviceItem>(); String
         * selectQuery = "SELECT * from Device"; int a = 0; SQLiteDatabase db =
         * this.getReadableDatabase(); Cursor cursor = db.rawQuery(selectQuery,
         * null);
         * 
         * try { if (cursor.moveToFirst()) { do { DeviceItem device = new
         * DeviceItem();
         * device.setDeviceId(Integer.parseInt(cursor.getString(0))); a =
         * device.getDeviceId(); device.setDeviceName(cursor.getString(1));
         * device.setDeviceIcon(cursor.getString(2));
         * device.setDeviceType(Integer.parseInt(cursor.getString(3)));
         * list.add(device);
         * 
         * } while (cursor.moveToNext()); }
         * 
         * } finally { if (cursor != null) { cursor.close(); } if (db != null) {
         * db.close(); } } return a;
         */
    }
    
    /*
     * Update placeId for list Device
     */
    public boolean updatePlaceIdForListDevice(ArrayList<DeviceItem> listDevice,
            int placeId) {
        Log.d("SQLite Helper Device", "updatePlaceIdForListDevice");
        
        SQLiteDatabase db = null;
        
        try {
            if (listDevice.size() > 0) {
                db = this.getReadableDatabase();
                if (db != null) {
                    String whereClause = "";
                    
                    for (DeviceItem deviceItem : listDevice) {
                        whereClause += "'" + deviceItem.getDeviceId() + " ' ,";
                    }
                    
                    whereClause = whereClause.substring(0,
                            whereClause.length() - 1);
                    whereClause = KEY_DEVICE_ID + " IN  (" + whereClause + ") ";
                    
                    ContentValues values = new ContentValues();
                    
                    values.put(KEY_DEVICE_PLACE_ID, placeId);
                    db.update(TABLE_DEVICE, values, whereClause, null);
                    db.close();
                }
            }
        } catch (Exception e) {
            Log.e("TEST_TEST_UPDATE_SUCCESS",
                    "TEST_TEST_UPDATE_SUCCESS:" + e.toString());
        } finally {
            if (db != null) {
                db.close();
            }
        }
        
        try {
            if (listDevice.size() > 0) {
                long time = System.currentTimeMillis();
                for (DeviceItem deviceItem : listDevice) {
                    /*
                     * HistoryChangePlaceOfDevice item = new
                     * HistoryChangePlaceOfDevice();
                     * item.setDateTimeAdd(String.valueOf(time));
                     * item.setPlaceId(placeId); item.setMemberChangeId(1);
                     * item.setDeviceId(deviceItem.getDeviceId()); //
                     * addHistoryChangePlaceOfDevice(item); HuyPVA-
                     */
                }
                return true;
            }
        } catch (Exception e) {
        }
        return false;
    }
    
    public int changePlaceforDeviceItemList(ArrayList<DeviceItem> lstDevice,
            PlaceItem place) {
        ContentValues values = new ContentValues();
        values.put(KEY_DEVICE_PLACE_ID, place.getPlaceId());
        
        ArrayList<String> whereArgs = new ArrayList<String>();
        ArrayList<String> whereClause = new ArrayList<String>();
        
        for (DeviceItem device : lstDevice) {
            whereClause.add(KEY_DEVICE_ID + "=?");
            whereArgs.add(device.getDeviceId() + "");
        }
        
        String where = TextUtils.join(" OR ", whereClause);
        
        return sqlUpdate(TABLE_DEVICE, values, where,
                whereArgs.toArray(new String[whereArgs.size()]));
    }
    
    public int changeFavoriteForDeviceList(ArrayList<DeviceItem> lstDevice) {
        ContentValues values = new ContentValues();
        
        int result = 0;
        SQLiteDatabase db = this.getWritableDatabase();
        
        try {
            
            // START TRANSACTION
            db.beginTransactionNonExclusive();
            
            for (DeviceItem device : lstDevice) {
                ArrayList<String> whereArgs = new ArrayList<String>();
                ArrayList<String> whereClause = new ArrayList<String>();
                
                values.put(KEY_DEVICE_FAVORITE, device.getFavorite());
                
                whereClause.add(KEY_DEVICE_ID + "=?");
                whereArgs.add(device.getDeviceId() + "");
                
                String where = TextUtils.join(" OR ", whereClause);
                
                try {
                    result = db.update(TABLE_DEVICE, values, where,
                            whereArgs.toArray(new String[whereArgs.size()]));
                    if (result == 0) {
                        return 0;
                    }
                } catch (Exception e) {
                    Log.e("bulkUpdateDevices(ArrayList<DeviceItem>, String, String",
                            e.getMessage());
                    
                }
            }
            // TRANSACTION SUCCESSFUL
            db.setTransactionSuccessful();
        } catch (Exception e) {
            Log.e("DATABASE ERROR", "Multi thread access denied");
            return -1;
        } finally {
            if (db != null) {
                // END TRANSACTION
                db.endTransaction();
                db.close();
            }
        }
        
        return result;
    }
    
    private int bulkUpdateDevices(ArrayList<DeviceItem> lstDevice,
            String fieldName, String fieldValue) {
        Log.d("SQLite Helper Device",
                "bulkUpdateDevices(ArrayList<DeviceItem>, String, String)");
        
        ContentValues values = new ContentValues();
        values.put(fieldName, fieldValue);
        
        ArrayList<String> whereArgs = new ArrayList<String>();
        ArrayList<String> whereClause = new ArrayList<String>();
        
        for (DeviceItem device : lstDevice) {
            whereClause.add(KEY_DEVICE_ID + "=?");
            whereArgs.add(device.getDeviceId() + "");
        }
        
        String where = TextUtils.join(" OR ", whereClause);
        
        int result = 0;
        SQLiteDatabase db = this.getWritableDatabase();
        db.beginTransactionNonExclusive();
        try {
            result = db.update(TABLE_DEVICE, values, where,
                    whereArgs.toArray(new String[whereArgs.size()]));
            if (result == lstDevice.size()) {
                db.setTransactionSuccessful();
            } else {
                result = 0;
            }
        } catch (Exception e) {
            Log.e("bulkUpdateDevices(ArrayList<DeviceItem>, String, String)",
                    e.getMessage());
        } finally {
            if (db != null) {
                db.endTransaction();
                db.close();
            }
        }
        
        return result;
    }
    
}
